var express = require('express');
var app = express();
//相对于当前的node_moduels文件夹
var mysqlCon = require('../util/mysqlCon.js')
var db = require('../util/mysqlExecute.js')
var formidable = require('formidable');
/*-----------------------------用于express路由----------------------------------*/
//人事管理登录
 var personLogin = function (req,res){
     console.log(req.body)
    //接受表单信息
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        // console.log(fields)

        console.log(`username:${fields.username}, password:${fields.password} `)

        var con = mysqlCon.getCon();
        // var sql = "select * from user"
        var sql ="SELECT `user`.id, role.role_name, `user`.`password`, `user`.`name` FROM `user` INNER JOIN role ON `user`.role = role.id"

        db.selectSql(con, sql, function (result) {
            // console.log(result)
            // res.json(result)
            for( var  i = 0; i <= result.length -1; i++){
                console.log(result[i])
                if( result[i].name == fields.username &&  result[i].password == fields.password && result[i].role_name.indexOf("人事") != -1){
                    console.log("登录成功:" ,result[i].name,  result[i].role_name)
                    //设置session
                    req.session.login = "1"
                    req.session.Id = result[i].id
                    req.session.name = result[i].name
                    req.session.position = result[i].role_name
                    // console.log(req.session.login, req.session.name)
                    //返回信息
                    var data = result[i]
                    res.json({data, "message": "success", "status":"200"})
                    return
                    // break;
                }
            }

            res.json({"message": "err", "status":"403"})
        })
    });

}
//薪酬管理登录
var salaryLogin = function (req,res){
    //接受表单信息
    console.log(req.username)
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(`username:${fields.username}, password:${fields.password} `)

        var con = mysqlCon.getCon();
        // var sql = "select * from user"
        var sql ="SELECT `user`.id, role.role_name, `user`.`password`, `user`.`name` FROM `user` INNER JOIN role ON `user`.role = role.id"

        db.selectSql(con, sql, function (result) {
            // console.log(result)
            // res.json(result)
            for( var  i = 0; i <= result.length -1; i++){
                console.log(result[i])
                if( result[i].name == fields.username &&  result[i].password == fields.password && result[i].role_name.indexOf("薪酬") != -1 ){
                    console.log("登录成功: " , result[i].role_name, result[i].role_name)
                    //设置session
                    req.session.login = "1"
                    req.session.Id = result[i].id
                    req.session.name = result[i].name
                    req.session.position = result[i].role_name

                    //返回信息
                    var data = result[i]
                    res.json({data, "message": "success", "status":"200"})
                    return
                    // break;
                }
            }

            res.json({"message": "err", "status":"403"})
        })
    });

}
// 添加档案
var addEmployee = function (req, res) {
    console.log(req.query)//get请求
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        // console.log(err,fields)//post请求
        //----------------------------------------------------
        fields.third_org = parseInt(fields.third_org)
        console.log(fields.employeeName)
        console.log(fields.age)
        console.log(fields.sex)
        console.log(fields.email)
        console.log(fields.phone)
        console.log(fields.qq)
        console.log(fields.telephone)
        console.log(fields.address)
        console.log(fields.postCode)
        console.log(fields.nationality)
        console.log(fields.birthPlace)
        console.log(fields.birthday)
        console.log(fields.nation)
        console.log(fields.faith)
        console.log(fields.politicalStatus)
        console.log(fields.idcard)
        console.log(fields.soSeNum)
        console.log(fields.edubg)
        console.log(fields.major)
        console.log(fields.bank)
        console.log(fields.accountNumber)
        console.log(fields.hobby)
        console.log(fields.resume)
        console.log(fields.familyRelative)
        console.log(fields.remark)
        console.log(fields.third_org)
        console.log(fields.second_org)
        console.log(fields.first_org)
        console.log(fields.professional)
        console.log(fields.creatTime)
        console.log(fields.registrant)
        var date = new Date().getFullYear() + ''
        //编号
        var num = "01"
        // var fileNum = date + fields.first_org  + fields.second_org  + fields.third_org  + num
        // isDelete, isCheck, fileNUm
        var data = new Date().getFullYear()+""
        var  fileNum = data + fields.first_org + fields.second_org + fields.third_org + num
        console.log(fileNum)

        var con = mysqlCon.getCon();
        var  addSql = 'INSERT INTO employees(id ,name, age, sex, email, phone, qq, telephone, address, postCode, nationality, birthPlace, birthday, nation, faith, politicalStatus, idcard, soSeNum, edubg, major, bank, accountNumber, hobby, resume, familyRelative, remark, thirdOrg, professional, creatTime, registrant, isDelete, isCheck, fileNum) VALUES(0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        var  addSqlParams = [fields.employeeName, fields.age, fields.sex, fields.email, fields.phone, fields.qq, fields.telephone, fields.address, fields.postCode, fields.nationality, fields.birthPlace, fields.birthday, fields.nation, fields.faith, fields.politicalStatus, fields.idcard, fields.soSeNum,fields.edubg,fields.major, fields.bank, fields.accountNumber, fields.hobby, fields.resume , fields.familyRelative, fields.remark, fields.third_org, fields.professional, fields.creatTime, fields.registrant,  0, 0, fileNum];

        // var  addSqlParams = ['碧峰2', 20, 0, '123456@174.com','84913246','123456800', '13420120011','广东省湛江市','511212','中国','广州市', '1997-10-06', '汉', '无', '团员', '44018115000925133', '44018115000925133','本科', '软件工程', '1255631231561315615616', '123456789514563', '唱歌', '无' , '无', '没有', 13, 3, '2017-12-16', 1, 0, 0, 201701112101];
        //isDelete, isCheck 默认为1

        db.addSql(con, addSql, addSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                var data = result
                res.json({data, "message":"success", "status":"200"})
            }else{
                res.json({"message":"err", "status":"404"})
            }
        })
    })

}
//根据档案查询档案
var selectEmployee = function (req, res) {
    // console.log(req.query)//get
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(fields.fileNum)
        var con = mysqlCon.getCon();
        var sql = `SELECT
employees.id,
employees.name,
employees.age,
employees.sex,
employees.email,
employees.phone,
employees.qq,
employees.telephone,
employees.address,
employees.postCode,
employees.nationality,
employees.birthPlace,
employees.birthday,
employees.nation,
employees.faith,
employees.politicalStatus,
employees.idcard,
employees.soSeNum,
employees.edubg,
employees.major,
employees.bank,
employees.accountNumber,
employees.hobby,
employees.resume,
employees.familyRelative,
employees.remark,
employees.thirdOrg,
employees.professional,
employees.creatTime,
employees.registrant,
employees.isDelete,
employees.isCheck,
employees.fileNum,
third_org.third_org,
second_org.second_org,
first_org.first_org,
position_type.position_type,
position_name.position_name,
position_title.position_title
FROM
employees
INNER JOIN third_org ON employees.thirdOrg = third_org.id
INNER JOIN second_org ON third_org.parentOrg = second_org.id
INNER JOIN first_org ON second_org.parentOrg = first_org.id
INNER JOIN position_title ON employees.professional = position_title.id
INNER JOIN position_name ON position_title.parentName = position_name.id
INNER JOIN position_type ON position_name.parentType = position_type.id
where employees.fileNum = ${fields.fileNum}
`
        db.selectSql(con, sql, function (result) {
            console.log(result)
            if(result){
                var data = result
                res.json({data, "message": "success", "status": "200"})
            }else{
                res.json({"message": "err", "status": "404"})
            }
        })
    })

}

//按条件查询档案
var switchSelEmployee  = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        var con = mysqlCon.getCon();
        // var sql = "SELECT employees.fileNum, first_org.first_org, second_org.second_org, third_org.third_org, employees.`name`,employees.age, employees.isCheck, position_name.position_name FROM employees INNER JOIN third_org ON employees.thirdOrg = third_org.id INNER JOIN second_org ON third_org.parentOrg = second_org.id INNER JOIN first_org ON second_org.parentOrg = first_org.idINNER JOIN position_title ON employees.professional = position_title.id INNER JOIN position_name ON position_title.parentName = position_name.id"
       var sql = "SELECT\n" +
           "employees.fileNum,\n" +
           "first_org.first_org,\n" +
           "second_org.second_org,\n" +
           "third_org.third_org,\n" +
           "employees.`name`,\n" +
           "employees.sex,\n" +
           "employees.isDelete,\n" +
           "employees.isCheck,\n" +
           "position_name.position_name\n" +
           "FROM\n" +
           "employees\n" +
           "INNER JOIN third_org ON employees.thirdOrg = third_org.id\n" +
           "INNER JOIN second_org ON third_org.parentOrg = second_org.id\n" +
           "INNER JOIN first_org ON second_org.parentOrg = first_org.id\n" +
           "INNER JOIN position_title ON employees.professional = position_title.id\n" +
           "INNER JOIN position_name ON position_title.parentName = position_name.id"
        db.selectSql(con, sql, function (result) {
            console.log(result)
            if(result){
                var data = result
                res.json({data, "message": "success", "status": "200"})
            }else{
                res.json({"message": "err", "status": "404"})
            }
        })
    })

}


// 修改档案
var updateEmployee = function (req, res) {
    // console.log('所有'+req.query)
    // console.log(req.query.fileNum)
    // var updateFile = req.query.fileNum
    // //动态创建sql语句
    // var keys = ''
    // var modSqlParams = [];
    // for(var key in req.query){
    //     if(key == 'fileNum'){
    //         break
    //     }
    //     keys += key + '= ?,'
    //     modSqlParams.push(req.query[key])
    // }
    // keys = keys.substring(0,keys.length -1)
    // console.log("最后的语句："+ keys);
    // console.log("modSqlParams："+ modSqlParams);

    var form1 = new formidable.IncomingForm()
    form1.parse(req, function (err, fields) {
        console.log(req.query,err,fields)
        //动态创建sql语句
        var keys = ''
        var modSqlParams = [];
        for(var key in fields){
            if(key == 'fileNum'){
               continue
            }
            keys += key + '= ?,'
            modSqlParams.push(fields[key])
        }
        keys = keys.substring(0,keys.length -1)
        console.log("最后的语句："+ keys);
        console.log("modSqlParams："+ modSqlParams);

        //获取档案Id值
        // console.log(updateFile)
        var con = mysqlCon.getCon();
        var modSql = `UPDATE employees SET ${keys}  WHERE fileNum = ${fields.fileNum}`;

         // var modSql = `UPDATE employees SET ${keys}  WHERE fileNum = ${updateFile}`;

        console.log(modSql)


        db.updateSql(con, modSql, modSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                var data = result
                res.json({data, "message": "success", "status": "200"})
            }else{
                res.json({"message": "err", "status": "404"})
            }
        })
    })
}
//删除档案
var deleteEmployee = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(`删除档案ID:${req.query.fileNum}`)
        console.log(fields.fileNum)
        var con = mysqlCon.getCon();
        var delSql  = `UPDATE employees SET isDelete = ? where fileNum=${fields.fileNum}`;
        var delSqlParams = [1];

        db.updateSql(con, delSql, delSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                res.json({ "message": "success", "status": "200", result})
            }else{
                res.json({"message": "err", "status": "404", "data": null})
            }

        })
    })

}
//复核档案
var checkEmployee = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(`复核档案ID:${req.query.fileNum}`)
        console.log(fields.fileNum);
        var con = mysqlCon.getCon();
        var delSql  = "UPDATE  employees SET isCheck = ? where fileNum = ?";
        console.log(delSql)
        var delSqlParams = ['1', fields.fileNum];

        db.updateSql(con, delSql, delSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                res.json({ "message": "success", "status": "200", result})
            }else{
                res.json({"message": "err", "status": "404", "data": null})
            }

        })
    })

}
//获取所有机构名
var selectOrg = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        var con = mysqlCon.getCon();
        var sql = "SELECT third_org.third_org, second_org.second_org, first_org.first_org FROM third_org INNER JOIN second_org ON third_org.parentOrg = second_org.id INNER JOIN first_org ON second_org.parentOrg = first_org.id"
        db.selectSql(con, sql, function (result) {
            console.log(result)
            if(result){
                // 定义三个机构数据
                let first_org = []
                let second_org = []
                let third_org = []
                //获取数据 遍历数据
                var data = result
                for(var i = 0;  i <= result.length - 1; i++){
                    // console.log(result[i].first_org)
                    //循环first_org
                    for(var j = 0; j <= first_org.length; j++){
                        if(first_org.indexOf(result[i].first_org) == -1){
                            first_org.push(result[i].first_org)
                        }
                    }
                    for(var k = 0; k <= second_org.length; k++){
                        if(second_org.indexOf(result[i].second_org) == -1){
                            second_org.push(result[i].second_org)
                        }
                    }
                    for(var l = 0; l <= third_org.length; l++){
                        if(third_org.indexOf(result[i].third_org) == -1){
                            third_org.push(result[i].third_org)
                        }
                    }
                }
                console.log(first_org)
                console.log(second_org)
                console.log(third_org)
                res.json({"message": "success", "status": "200", first_org, second_org, third_org})
            }else{
                res.json({"message": "err", "status": "404"})
            }
        })
    })

}
//获取所有职称名
var selectPosition = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        var con = mysqlCon.getCon();
        var sql = "SELECT position_title.position_title, position_name.position_name, position_type.position_type FROM position_title\n" +
            "INNER JOIN position_name ON position_title.parentName = position_name.id INNER JOIN position_type ON position_name.parentType = position_type.id"
        db.selectSql(con, sql, function (result) {
            console.log(result)
            if(result){
                // 定义三个机构数据
                let position_type = []
                let position_name = []
                let position_title = []
                //获取数据 遍历数据
                var data = result
                console.log(data)
                // for(var i = 0;  i <= result.length - 1; i++){
                //     // console.log(result[i].first_org)
                //     //循环first_org
                //     for(var j = 0; j <= position_type.length; j++){
                //         if(position_type.indexOf(result[i].position_type) == -1){
                //             position_type.push(result[i].position_type)
                //         }
                //     }
                //     for(var k = 0; k <= position_name.length; k++){
                //         if(position_name.indexOf(result[i].position_name) == -1){
                //             position_name.push(result[i].position_name)
                //         }
                //     }
                //     for(var l = 0; l <= position_title.length; l++){
                //         if(position_title.indexOf(result[i].position_title) == -1){
                //             position_title.push(result[i].position_title)
                //         }
                //     }
                // }
                // console.log(position_type)
                // console.log(position_name)
                // console.log(position_title)

                res.json({ "message": "success", "status": "200", data})
            }else{
                res.json({"message": "err", "status": "404"})
            }
        })
    })

}
//薪酬登记
var addSalary = function (req, res) {
    console.log(req.query)//get请求
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {

    console.log(fields.name)
    console.log(fields.baseWage)
    console.log(fields.registrant)
    console.log(fields.creatTime)
    console.log(fields.register)
    console.log(fields.total)
        // console.log(req.query.name)
        // console.log(req.query.baseWage)
        // console.log(req.query.registrant)
        // console.log(req.query.creatTime)
        // console.log(req.query.register)


        var con = mysqlCon.getCon();
        var  addSql = 'INSERT INTO salary_standard(id ,name, baseWage, registrant, creatTime, isCheck, register, total, isDelete) VALUES(0,?,?,?,?,?,?,?,?)';
        var  addSqlParams = [fields.name, fields.baseWage, fields.registrant, fields.creatTime, '0', fields.register, fields.total, '0'];


        db.addSql(con, addSql, addSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                var data = result
                res.json({data, "message":"success", "status":"200"})
            }else{
                res.json({"message":"err", "status":"404"})
            }
        })
    })

}
//薪酬复核
var checkSalary = function (req, res) {
    console.log(req.query.id)
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(`复核薪酬ID:${fields.id}`)
        var con = mysqlCon.getCon();
        var delSql  = `UPDATE salary_standard SET isCheck = ? where id=${req.query.id}`;
        var delSqlParams = [1];

        db.updateSql(con, delSql, delSqlParams, function (result) {
            console.log(result)
            if(result.changedRows == 1){
                res.json({ "message": "success", "status": "200", result})
            }else{
                res.json({"message": "err", "status": "404", "data": null})
            }

        })
    })

}
//查看薪酬
var selectSalary = function (req, res) {

    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        var con = mysqlCon.getCon();
        var sql = "select * from salary_standard"
        db.selectSql(con, sql, function (result) {
            console.log(result)
            if(result.length){
                res.json({result, "message":"success", "status": "200" })

            }else{
                res.json({"message":"err", "status": "404" })

            }
        })
    })

}

//按照id查看薪酬记录表
var selectSalaryID = function (req, res) {
        console.log(req.query)
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(fields.id)
        var con = mysqlCon.getCon();
        var sql = `select * from salary_standard where id = ${req.query.id}`
        db.selectSql(con, sql, function (result) {
            console.log(result)
            res.json(result)
        })
    })

}
//删除薪酬
var deleteSalary = function (req, res) {
    var form = new formidable.IncomingForm()
    form.parse(req, function (err, fields) {
        console.log(`删除薪酬ID:${req.query.id}`)
        console.log(fields.id)
        var con = mysqlCon.getCon();
        var delSql  = `UPDATE salary_standard SET isDelete = ? where id=${req.query.id}`;
        var delSqlParams = [1];

        db.updateSql(con, delSql, delSqlParams, function (result) {
            console.log(result)
            if(result.affectedRows == 1){
                res.json({ "message": "success", "status": "200", result})
            }else{
                res.json({"message": "err", "status": "404", "data": null})
            }

        })
    })

}

module.exports = {
    personLogin: personLogin,
    salaryLogin: salaryLogin,
    addEmployee: addEmployee,
    selectEmployee: selectEmployee,
    switchSelEmployee: switchSelEmployee ,
    updateEmployee: updateEmployee,
    deleteEmployee: deleteEmployee,
    checkEmployee: checkEmployee,
    selectOrg: selectOrg,
    selectPosition: selectPosition,
    addSalary: addSalary,
    checkSalary: checkSalary,
    selectSalary: selectSalary,
    selectSalaryID: selectSalaryID,
    deleteSalary: deleteSalary,
}